package com.fcscs.cservice.dao.hibernate;

import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.fcscs.core.dao.ConnectionManager;
import com.fcscs.core.domain.BaseEntity.DBLoc;
import com.fcscs.cservice.common.CServiceCommonEnums.ModuleEnum;
import com.fcscs.cservice.common.CServiceCommonEnums.UpdateVerLevelEnum;
import com.fcscs.cservice.common.CServiceCommonEnums.VersionEntityTypeEnum;
import com.fcscs.cservice.common.CServiceCommonEnums.VersionTypeEnum;
import com.fcscs.cservice.dao.ECMSVersionDao;
import com.fcscs.cservice.domain.VersionUpdates;
import com.fcscs.cservice.domain.Versions;
import com.fcscs.cservice.domain.model.VersionView;

@Repository("ecmsVersionDao")
public class ECMSVersionDaoHibernate implements ECMSVersionDao {

	@Autowired
	private ConnectionManager connMgr;

	private static DBLoc VDBLoc = new Versions().getDBLoc();
	private static Logger LOGGER = Logger.getLogger(ECMSVersionDaoHibernate.class);
	
	private static final String GET_VERS = " from Versions v where v.orgId = :orgId and v.propertyId = :propId and module = :module ";
	private static final String BY_TYPE = " and versionType = :verType ";
	private static final String BY_ENT_TYPE = " and entityType = :entType ";
	private static final String BY_TMPL_NAME = " and templateName = :tmplName ";
	private static final String BY_ENT_ID = " and entityId = :entId ";
	private static final String DEL_VERS = " update Versions set active = false where id = :id ";
	private static final String DEL_VER_UPDS = " update VersionUpdates set active = false where id = :id ";
	private static final String VERS_CNT = " select count(*) from Versions where orgId = :orgId and propertyId = :propId " +
			" and module = :module and id <> :id ";
	private static final String VER_UPDS_CNT = " select count(*) from VersionUpdates where orgId = :orgId and " +
			" propertyId = :propId and module = :module and id <> :id ";
	
	private static final String IS_ACTIVE = " and active = true ";
	
	private static final String OVER_VERSION = " update versions set version_type = :verType, active = false where id = :id ";
	private static final String DEL_VER_UPD = " delete from version_updates where org_id = :orgId and property_id = :propId " +
			" and module = :module and active = true and entity_id is not null ";
	private static final String INC_VER_UPD = " update version_updates set version_value = version_value + 1 " +
			" where org_id = :orgId and property_id = :propId and module = :module and active = true and entity_id is null ";
	private static final String GET_EXP_SCH = " select * from versions where org_id = :orgId and property_id = :propId " +
			" and version_type = :verType and active = true and start_date > now() ";

	private static final String GET_VER_UPDATES = " from VersionUpdates v where v.orgId = :orgId and v.propertyId = :propId and module = :module ";

	private static final String INC_VER = " update version_updates set version_value = version_value + 1 " +
			" where org_id = :orgId and property_id = :propId and module = :module and active = true ";
	private static final String ENT_LV = " and entity_type = :entType and entity_id = :entId ";
	private static final String TYP_LV = " and entity_type = :entType and entity_id is null ";
	private static final String MOD_LV = " and entity_type is null and entity_id is null ";
	
	private static final String GET_VER_VALUE = " select (case version_value when null then 0 else 1 end) as version_value from version_updates " +
			" where org_id = :orgId and property_id = :propId and module = :module and active = true ";

	@SuppressWarnings("unchecked")
	@Override
	public List<Versions> getVersions(long orgId, long propId, ModuleEnum module, VersionTypeEnum versionType, VersionEntityTypeEnum entityType) {
		Query q = connMgr.getCurrentSession(VDBLoc).createQuery(GET_VERS + (versionType == null ? "" : BY_TYPE)
				+ (entityType == null ? "" : BY_ENT_TYPE) + IS_ACTIVE);
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);
		q.setString("module", module.toString());
		if (versionType != null) {
			q.setString("verType", versionType.toString());
		}
		if (entityType != null) {
			q.setString("entType", entityType.toString());
		}
		return q.list();
	}

	@Override
	public Versions getVersion(long id) {
		return (Versions) connMgr.getCurrentSession(VDBLoc).get(Versions.class, id);
	}

	@Override
	public Versions saveVersion(Versions ver) {
		connMgr.getCurrentSession(VDBLoc).saveOrUpdate(ver);
		return ver;
	}

	@Override
	public VersionUpdates saveVersionUpdates(VersionUpdates vu) {
		connMgr.getCurrentSession(VDBLoc).saveOrUpdate(vu);
		return vu;
	}

	@Override
	public boolean deleteVersion(Versions ver) {
		return connMgr.getCurrentSession(VDBLoc).createQuery(DEL_VERS).setLong("id", ver.getId()).executeUpdate() == 1;
	}

	@Override
	public boolean deleteVersionUpdates(VersionUpdates ver) {
		return connMgr.getCurrentSession(VDBLoc).createQuery(DEL_VER_UPDS).setLong("id", ver.getId()).executeUpdate() == 1;
	}

	@Override
	public boolean permDeleteVersion(Versions ver) {
		connMgr.getCurrentSession(VDBLoc).delete(ver);
		return true;
	}

	@Override
	public boolean permDeleteVersionUpdates(VersionUpdates vu) {
		connMgr.getCurrentSession(VDBLoc).delete(vu);
		return true;
	}

	@Override
	public boolean isVersionTaken(long orgId, long propId, ModuleEnum module, VersionTypeEnum versionType, VersionEntityTypeEnum entityType, String name, Long id) {
		Query q = connMgr.getCurrentSession(VDBLoc).createQuery(VERS_CNT + BY_TYPE
				+ (entityType == null ? "" : BY_ENT_TYPE) + (name == null ? "" : BY_TMPL_NAME) + IS_ACTIVE);
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);
		q.setString("module", module.toString());
		q.setString("verType", versionType.toString());
		q.setLong("id", id == null ? -1 : id);
		if (entityType != null) {
			q.setString("entType", entityType.toString());
		}
		if (name != null) {
			q.setString("tmplName", name);
		}
		return ((Number) q.uniqueResult()).intValue() > 0;
	}

	@Override
	public boolean isVersionUpdatesTaken(long orgId, long propId, ModuleEnum module, VersionEntityTypeEnum entityType,
			Long entityId, Long id) {
		Query q = connMgr.getCurrentSession(VDBLoc).createQuery(VER_UPDS_CNT + (entityType == null ? "" : BY_ENT_TYPE)
				+ (entityId == null ? "" : BY_ENT_ID) + IS_ACTIVE);
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);
		q.setString("module", module.toString());
		q.setLong("id", id == null ? -1 : id);
		if (entityType != null) {
			q.setString("entType", entityType.toString());
		}
		if (entityId != null) {
			q.setLong("entId", entityId);
		}
		return ((Number) q.uniqueResult()).intValue() > 0;
	}
	
	@Override
	public boolean invalidateVersion(Versions ver, VersionTypeEnum setToType, boolean isCurrent) {
		Query q = connMgr.getCurrentSession(VDBLoc).createSQLQuery(OVER_VERSION);
		q.setLong("id", ver.getId());
		q.setString("verType", setToType.toString());
		q.executeUpdate();
		if (isCurrent) {
			Query q1 = connMgr.getCurrentSession(VDBLoc).createSQLQuery(DEL_VER_UPD);
			q1.setLong("orgId", ver.getOrgId());
			q1.setLong("propId", ver.getPropertyId());
			q1.setString("module", ver.getModuleEnum().toString());
			q1.executeUpdate();
			Query q2 = connMgr.getCurrentSession(VDBLoc).createSQLQuery(INC_VER_UPD);
			q2.setLong("orgId", ver.getOrgId());
			q2.setLong("propId", ver.getPropertyId());
			q2.setString("module", ver.getModuleEnum().toString());
			q2.executeUpdate();
		}
		return true;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Versions> getExpiredVersionList(long orgId, long propId) {
		SQLQuery q = connMgr.getCurrentSession(VDBLoc).createSQLQuery(GET_EXP_SCH);
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);
		q.setString("verType", VersionTypeEnum.SCHEDULED.toString());
		q.addEntity(Versions.class);
		return q.list();
	}

	@Override
	public List<VersionView> getVersionViewList(long orgId, long propId, ModuleEnum module, List<VersionView> verList) {
		List<VersionView> resultList = new ArrayList<VersionView>(verList.size());
		for (VersionView v : verList) {
			UpdateVerLevelEnum level = UpdateVerLevelEnum.MOD;
			VersionEntityTypeEnum typeEnum = v.getEntityType() == null ? null : VersionEntityTypeEnum.valueOf(v.getEntityType());
			if (v.getEntityId() != null) {
				level = UpdateVerLevelEnum.ENT;
			} else if (typeEnum != null) {
				level = UpdateVerLevelEnum.TYPE;
			}
			v.setVersion(getUpdateVersionValues(orgId, propId, module, typeEnum, v.getEntityId(), level));
			resultList.add(v);
		}
		return resultList;
	}

	private long getUpdateVersionValues(long orgId, long propId, ModuleEnum module, VersionEntityTypeEnum entityType, Long entityId, UpdateVerLevelEnum level) {
		Query q1 = connMgr.getCurrentSession(VDBLoc).createSQLQuery(GET_VER_VALUE + (UpdateVerLevelEnum.ENT.equals(level) ? ENT_LV : (UpdateVerLevelEnum.TYPE.equals(level)) ? TYP_LV : MOD_LV));
		q1.setLong("orgId", orgId);
		q1.setLong("propId", propId);
		q1.setString("module", module.toString());
		if (UpdateVerLevelEnum.ENT.equals(level)) {
			q1.setString("entType", entityType.toString());
			q1.setLong("entId", entityId);
		} else if (UpdateVerLevelEnum.TYPE.equals(level)) {
			q1.setString("entType", entityType.toString());
		}
		return ((Number)q1.uniqueResult()).longValue();
	}

	@Override
	public void updateVersionValues(long orgId, long propId, ModuleEnum module, VersionEntityTypeEnum entityType, Long entityId, UpdateVerLevelEnum level) {
		switch (level) {
		case ENT:
			exeUpdateVersionValues(orgId, propId, module, entityType, entityId, UpdateVerLevelEnum.ENT);
		break;
		case ENT_TYPE:
			exeUpdateVersionValues(orgId, propId, module, entityType, entityId, UpdateVerLevelEnum.ENT);
			exeUpdateVersionValues(orgId, propId, module, entityType, entityId, UpdateVerLevelEnum.TYPE);
		break;
		case ENT_TYPE_MOD:
			exeUpdateVersionValues(orgId, propId, module, entityType, entityId, UpdateVerLevelEnum.ENT);
			exeUpdateVersionValues(orgId, propId, module, entityType, entityId, UpdateVerLevelEnum.TYPE);
			exeUpdateVersionValues(orgId, propId, module, entityType, entityId, UpdateVerLevelEnum.MOD);
		break;
		case TYPE:
			exeUpdateVersionValues(orgId, propId, module, entityType, entityId, UpdateVerLevelEnum.TYPE);
		break;
		case TYPE_MOD:
			exeUpdateVersionValues(orgId, propId, module, entityType, entityId, UpdateVerLevelEnum.TYPE);
			exeUpdateVersionValues(orgId, propId, module, entityType, entityId, UpdateVerLevelEnum.MOD);
		break;
		case MOD:
			exeUpdateVersionValues(orgId, propId, module, entityType, entityId, UpdateVerLevelEnum.MOD);
		break;
		}
	}
	
	private void exeUpdateVersionValues(long orgId, long propId, ModuleEnum module, VersionEntityTypeEnum entityType, Long entityId, UpdateVerLevelEnum level) {
		Query q1 = connMgr.getCurrentSession(VDBLoc).createSQLQuery(INC_VER + (UpdateVerLevelEnum.ENT.equals(level) ? ENT_LV : (UpdateVerLevelEnum.TYPE.equals(level)) ? TYP_LV : MOD_LV));
		q1.setLong("orgId", orgId);
		q1.setLong("propId", propId);
		q1.setString("module", module.toString());
		if (UpdateVerLevelEnum.ENT.equals(level)) {
			q1.setString("entType", entityType.toString());
			q1.setLong("entId", entityId);
		} else if (UpdateVerLevelEnum.TYPE.equals(level)) {
			q1.setString("entType", entityType.toString());
		}
		int updRow = q1.executeUpdate();
		if (updRow <= 0) {
			newUpdateVersionValues(orgId, propId, module, entityType, entityId, level);
		}
	}

	private void newUpdateVersionValues(long orgId, long propId, ModuleEnum module, VersionEntityTypeEnum entityType, Long entityId, UpdateVerLevelEnum level) {
		VersionUpdates vu = new VersionUpdates();
		vu.setOrgId(orgId);
		vu.setPropertyId(propId);
		vu.setModuleEnum(module);
		if (UpdateVerLevelEnum.ENT.equals(level)) {
			vu.setEntityTypeEnum(entityType);
			vu.setEntityId(entityId);
		} else if (UpdateVerLevelEnum.TYPE.equals(level)) {
			vu.setEntityTypeEnum(entityType);
		}
		vu.setVersionValue(1L);
		connMgr.getCurrentSession(VDBLoc).saveOrUpdate(vu);
	}

	@Override
	public Versions getCurrentVersion(long orgId, long propId, ModuleEnum module, VersionEntityTypeEnum entityType) {
		return getVersion(orgId, propId, module, VersionTypeEnum.CURRENT, entityType, null);
	}

	@Override
	public Versions getScheduledVersion(long orgId, long propId, ModuleEnum module, VersionEntityTypeEnum entityType) {
		return getVersion(orgId, propId, module, VersionTypeEnum.SCHEDULED, entityType, null);
	}

	@Override
	public Versions getVersion(long orgId, long propId, ModuleEnum module, VersionTypeEnum versionType, VersionEntityTypeEnum entityType, String name) {
		if (module == null || versionType == null || (VersionTypeEnum.TEMPLATE.equals(versionType)
				&& (name == null || "".equals(name.trim())))) {
			LOGGER.debug("getVersion invalid parameter");
			return null;
		}
		Query q = connMgr.getCurrentSession(VDBLoc).createQuery(GET_VERS + (versionType == null ? "" : BY_TYPE)
				+ (entityType == null ? "" : BY_ENT_TYPE) + (!VersionTypeEnum.TEMPLATE.equals(versionType) ? "" : BY_TMPL_NAME)
				+ IS_ACTIVE);
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);
		q.setString("module", module.toString());
		if (versionType != null) {
			q.setString("verType", versionType.toString());
		}
		if (entityType != null) {
			q.setString("entType", entityType.toString());
		}
		if (VersionTypeEnum.TEMPLATE.equals(versionType)) {
			q.setString("tmplName", name);
		}
		return (Versions) q.uniqueResult();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<VersionUpdates> getVersionUpdates(long orgId, long propId, ModuleEnum module, VersionEntityTypeEnum entityType) {
		Query q = connMgr.getCurrentSession(VDBLoc).createQuery(GET_VER_UPDATES + (entityType == null ? "" : BY_ENT_TYPE) + IS_ACTIVE);
		q.setLong("orgId", orgId);
		q.setLong("propId", propId);
		q.setString("module", module.toString());
		if (entityType != null) {
			q.setString("entType", entityType.toString());
		}
		return q.list();
	}
}
